package com.sixmac.user;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.springframework.util.CollectionUtils;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.List;
import java.util.Map;

/**
 * Created by xuxiang on 2017/6/23.
 */
public class ExcelUtil {

    public static HSSFWorkbook getHSSFWorkbook(String sheetName, String[] title, String[][] values, HSSFWorkbook wb) {
        // 第一步，创建一个webbook，对应一个Excel文件
        if (wb == null) {
            wb = new HSSFWorkbook();
        }
        // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet(sheetName);
        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
        HSSFRow row = sheet.createRow(0);
        // 第四步，创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式

        HSSFCell cell = null;
        //创建标题
        for (int i = 0; i < title.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(style);
        }
        //创建内容
        for (int i = 0; i < values.length; i++) {
            row = sheet.createRow(i + 1);
            for (int j = 0; j < values[i].length; j++) {
                row.createCell(j).setCellValue(values[i][j]);
            }
        }

        return wb;
    }

    /**
     * 生成excel
     *
     * @param templatePath   目标文件路径
     * @param titles         字段数据，中间用逗号隔开，例如：name,mobile,address
     * @param list           待导出的列表
     * @param exportFilePath 生成文件路径
     * @author gaolei
     */
    public static void createExcel(String templatePath, String titles, List<Map<String, Object>> list, String exportFilePath)
            throws Exception {
        System.out.println(exportFilePath + "-------------");
        // 读取导出模板
        FileInputStream fis = new FileInputStream(new File(templatePath));
        HSSFWorkbook workbook = new HSSFWorkbook(fis);
        HSSFCellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setAlignment(HorizontalAlignment.CENTER);
        // 输出流
        OutputStream out = new FileOutputStream(exportFilePath);
        // 模板数据要放在Sheet1
        HSSFSheet sheet = workbook.getSheet("记录清单");
        // 标题字段数据
        String[] titlesArr = titles.split(",");

        HSSFRow row;
        if(!CollectionUtils.isEmpty(list)){
            for (int i = 0; i < list.size(); i++) {
                Map<String, Object> map = list.get(i);
                row = sheet.createRow(1 + i);

                for (int j = 0; j < titlesArr.length; j++) {
                    HSSFCell cell = row.createCell(j);
                    String value = map.get(titlesArr[j]) + "";
                    cell.setCellValue(value);
                }

            }
        }
        workbook.write(out);
    }

    /**
     * 统一处理excel的导入数值类型问题，将所有内容都转为string处理
     *
     * @param cell
     * @return
     */
    public static String getCellValue(Cell cell) {
        Object obj = "";
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                obj = cell.getStringCellValue();
                break;
            case Cell.CELL_TYPE_NUMERIC:
                obj = cell.getNumericCellValue();
                break;
            case Cell.CELL_TYPE_FORMULA:
                obj = cell.getCellFormula();
                break;
            case Cell.CELL_TYPE_ERROR:
                obj = cell.getErrorCellValue();
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                obj = cell.getBooleanCellValue();
                break;
            case Cell.CELL_TYPE_BLANK:
                break;
        }

        return String.valueOf(obj).trim();
    }
}
